package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import modelo.Aula;
import modelo.Turma;
import conexao.ConnectionManager;

public class AulaDaoImp implements AulaDAO{
	
	private final String INSERT = "insert into aula (diaSemana, professor,materia, horaEntrada, horaSaida, totalDeAulas, senha, idturma) values (?,?,?,?,?,?,?,?)";
	private final String UPDATE = "update aula set diaSemana = ?, professor = ?, materia = ?, dataDeAula = ?, horaEntrada = ?, horaSaida = ?,totalDeAulas = ?, senha = ?, idturma = ? where idaula = ?";
	private final String DELETE = "delete from aula where idaula = ?";
	private final String ALL = "select * from aula";
	private final String ID = "select * from aula where idaula = ?";
	private final String DIASEMANA = "select * from aula where diaSemana = ?";
	private final String PROFESSOR = "select * from aula where professor = ?";
	private final String MATERIA = "select * from aula where materia = ?";
	private final String LOGAR = "select * from aula where professor = ? and senha = ?";
	private final String REDEFINIR = "update aula set senha  = ? where idaula = ?";
	private final String CHECAR = "select * from aula where professor = ?";
	private final String CHECARSEMANA = "select * from frequencia where diaSemana?";
	//private final String CHECARHORA = "select * from aula where horaEntrada = ? and horaSaida = ?";
	private final String BUSCAR_AULA = "SELECT * FROM aula WHERE idTurma = ? and diaSemana = ?";
	

	private static Connection connection;
	
	public AulaDaoImp() {
		openConnection();
	
	}
	public Aula checarSemana(String diaSemana){
		List<Aula> aulas= new ArrayList<Aula>();
		
		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(CHECARSEMANA);
			ps.setObject(1, diaSemana);
			
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setProfessor(rs.getString("professor"));
				aula.setMateria(rs.getString("materia"));
				aula.setDataDeAula(rs.getDate("dataDeAula"));
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idturma"));
				aula.setTurma(turma);
				aulas.add(aula);
			}
		} catch (SQLException e) {
			Logger.getLogger(FrequenciaDaoImp.class.getName()).log(Level.SEVERE,null, e);
		}finally{
			this.closeConnection();
		}
		if(aulas.isEmpty()){
			return null;
		}else{
			return aulas.get(0);
		}
	}
	public Aula redefinirSenha(Aula e){
		try{
			PreparedStatement ps = null;
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(REDEFINIR);
			ps.setObject(1, e.getSenha());
			ps.setObject(2, e.getIdAula());
			
			ps.executeUpdate();
			
		}catch(SQLException ex){
				Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null,ex);
				
			}finally{
				this.closeConnection();
			}
		return e;
		} 
	public Aula checarUsuario(String nome) {
		List<Aula> usuarios = new ArrayList<Aula>();
		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(CHECAR);
			ps.setObject(1, nome);
			
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setProfessor(rs.getString("professor"));
				aula.setMateria(rs.getString("materia"));
				//aula.setDataDeAula(rs.getDate("dataDeAula"));
				aula.setSenha(rs.getString("senha"));
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idturma"));
				aula.setTurma(turma);
				usuarios.add(aula);
				
			}
			
		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (usuarios.isEmpty()) {
			return null;
		} else {
			return usuarios.get(0);
		}
	}
	@Override
	public Aula logar(String professor, String senha) {
		List<Aula> professores = new ArrayList<Aula>();
		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(LOGAR);
			ps.setObject(1, professor);
			ps.setObject(2, senha);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setProfessor(rs.getString("professor"));
				aula.setSenha(rs.getString("senha"));
				professores.add(aula);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}if (professores.isEmpty()) {
			return null;
		} else {
			return professores.get(0);
		}
	}
	@Override
	public Aula salvar(Aula e) {
		try {
			PreparedStatement ps = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(INSERT);
			
			ps.setObject(1, e.getDiaSemana());
			ps.setObject(2, e.getProfessor());
			ps.setObject(3, e.getMateria());
			ps.setObject(4, e.getHoraEntrada());
			ps.setObject(5, e.getHoraSaida());
			ps.setObject(6, e.getTotalDeAulas());
			ps.setObject(7, e.getSenha());
			ps.setObject(8, e.getTurma().getIdTurma());
			
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return e;
	}

	@Override
	public void alterar(Aula e) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(UPDATE);
						
			ps.setObject(1, e.getDiaSemana());
			ps.setObject(2, e.getProfessor());
			ps.setObject(3, e.getMateria());
			ps.setObject(4, e.getHoraEntrada());
			ps.setObject(5, e.getHoraSaida());
			ps.setObject(6, e.getTotalDeAulas());
			ps.setObject(7, e.getSenha());
			ps.setObject(8, e.getTurma().getIdTurma());
			ps.setObject(9, e.getIdAula());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}

	@Override
	public void deletar(Aula e) {
		try {
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);

			ps.setObject(1, e.getIdAula());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}

	@Override
	public List<Aula> findAll() {
		List<Aula> aulas = new ArrayList<Aula>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(ALL);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setProfessor(rs.getString("professor"));
				aula.setMateria(rs.getString("materia"));
				aula.setDataDeAula(rs.getDate("dataDeAula"));
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idturma"));
				aula.setTurma(turma);
				aulas.add(aula);
			
			}

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return aulas;
	}

	@Override
	public void openConnection() {
		if ( connection == null) {
			connection = ConnectionManager.getInstance().getConnection();
		}
		
		if ( isConnectionClose() ) {
			connection = ConnectionManager.getInstance().getConnection();
		}		
	}

	@Override
	public void closeConnection() {
		if ( connection != null && !this.isConnectionClose()) {
			ConnectionManager.getInstance().closeConnection(connection);
		}
	
	}
	@Override
	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
	}
	@Override
	public Aula buscarPorID(int idAula) {
		List<Aula> aulas = new ArrayList<Aula>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(ID);
			ps.setObject(1, idAula);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setProfessor(rs.getString("professor"));
				aula.setMateria(rs.getString("materia"));
				//aula.setDataDeAula(rs.getDate("dataDeAula"));
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));

				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idturma"));
				aula.setTurma(turma);
				aulas.add(aula);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (aulas.isEmpty()) {
			return null;
		} else {
			return aulas.get(0);
		}
		
	}
	@Override
	public List<Aula> buscarPorDiaSemana(String diaSemana) {
		List<Aula> aulas = new ArrayList<Aula>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(DIASEMANA);
			ps.setObject(1, diaSemana);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setProfessor(rs.getString("professor"));
				aula.setMateria(rs.getString("materia"));
				//aula.setDataDeAula(rs.getDate("dataDeAula"));
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idturma"));
				aula.setTurma(turma);
				aulas.add(aula);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (aulas.isEmpty()) {
			return null;
		} else {
			return aulas;
		}
	}
	@Override
	public List<Aula> buscarPorProfessor(String professor) {
		List<Aula> aulas = new ArrayList<Aula>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(PROFESSOR);
			ps.setObject(1, professor);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setProfessor(rs.getString("professor"));
				aula.setMateria(rs.getString("materia"));
				//aula.setDataDeAula(rs.getDate("dataDeAula"));
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idturma"));
				aula.setTurma(turma);
				aulas.add(aula);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (aulas.isEmpty()) {
			return null;
		} else {
			return aulas;
		}
	}
	@Override
	public List<Aula> buscarPorMateria(String materia) {
		List<Aula> aulas = new ArrayList<Aula>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(MATERIA);
			ps.setObject(1, materia);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idaula"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setProfessor(rs.getString("professor"));
				aula.setMateria(rs.getString("materia"));
				//aula.setDataDeAula(rs.getDate("dataDeAula"));
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idturma"));
				aula.setTurma(turma);
				aulas.add(aula);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AulaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (aulas.isEmpty()) {
			return null;
		} else {
			return aulas;
		}
	}
	
	@Override
	public List<Aula> buscarPorAula(String diaSemana, int idTurma) {
		List<Aula> aulas = new ArrayList<Aula>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(BUSCAR_AULA);
			ps.setObject(1, idTurma);
			ps.setObject(2, diaSemana);
			System.out.println(BUSCAR_AULA);
			System.out.println(idTurma);
			System.out.println(diaSemana);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aula aula = new Aula();
				aula.setHoraEntrada(rs.getString("horaEntrada"));
				aula.setDiaSemana(rs.getString("diaSemana"));
				aula.setHoraSaida(rs.getString("horaSaida"));
				aula.setIdAula(rs.getInt("idAula"));
				aula.setMateria(rs.getString("materia"));
				aula.setProfessor(rs.getString("professor"));
				aula.setSenha(rs.getString("senha"));
				aula.setTotalDeAulas(rs.getInt("totalDeAulas"));
				aula.setTurma(DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idTurma")));
				aulas.add(aula);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}if (aulas.isEmpty()) {
			return null;
		} else {
			return aulas;
		}
	}
	

}
